package com.sztz.utils;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.sztz.enums.FirmBillTypeEnum;
import com.sztz.vo.FirmBillStatisticsExcelVo;

import java.io.File;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 处理平台单证统计数据
 * @author yzy
 * @date 2022/11/2 16:02
 */
public class FirmBillStatisticsUtils {
    /**
     * Excel文件基础路径
     */
    private static final String BASE_EXCEL_PATH="D:\\wx\\20221102\\";
    /**
     * 读取EXCEL文件，获取报关单录单统计数据
     * @return insert语名
     * @author yzy
     * @date 2022/11/2 16:02
     */
    public static List<FirmBillStatisticsExcelVo> readTdecCreateExcel(String excelPath){
        return EasyExcel.read(new File(BASE_EXCEL_PATH+excelPath)).head(FirmBillStatisticsExcelVo.class).sheet().doReadSync();
    }

    /**
     * 读取统计数据EXCEL文件，输出SQL文件
     * @param outFilePath 企业信息
     * @author yzy
     * @date 2022/11/2 17:00
     */
    private static void doOutSql(List<FirmBillStatisticsExcelVo> list,String outFilePath,String statisticsDate){
        List<String> sqlList = list.stream().map(item -> createInsertSql(item, statisticsDate)).collect(Collectors.toList());
        FileUtil.appendUtf8Lines(sqlList,new File(outFilePath));
    }
    /**
     * 组装插入单证统计信息的SQL脚本
     * @param excelVo 统计信息
     * @param statisticsDate 统计日期
     * @return String sql
     * @author yzy
     * @date 2022/11/2 16:29
     */
    private static String createInsertSql(FirmBillStatisticsExcelVo excelVo,String statisticsDate){
        String statisticsMonth = StrUtil.subPre(statisticsDate,7);
        String sql = "INSERT INTO TZ_CLOUD_GUARD.FIRM_BILL_STATISTICS(statistics_date,type,quantity,firm_id,area_agent_firm_id,sales_manage_id,sales_dept_id,customer_service_staff_id,implementer_id,customer_belong,firm_create_time,create_time,modify_time,deleted,modifier_id,statistics_month) " +
                "VALUES('{}',{},{},'{}','{}','{}','{}','{}','{}',{},'{}',now(),now(),0,'','{}');";
        return StrUtil.format(sql,statisticsDate,excelVo.getType(),excelVo.getQuantity(),excelVo.getFirmId(),StrUtil.blankToDefault(excelVo.getAreaAgentFirmId(),StrUtil.EMPTY),excelVo.getSalesManageId(),StrUtil.blankToDefault(excelVo.getSalesDeptId(),StrUtil.EMPTY),StrUtil.blankToDefault(excelVo.getCustomerServiceStaffId(),StrUtil.EMPTY),StrUtil.blankToDefault(excelVo.getImplementerId(),StrUtil.EMPTY),excelVo.getCustomerBelong(),excelVo.getFirmCreateTime(),statisticsMonth);
    }

    public static void main(String[] args) {
        String statisticsDate = "2022-11-01";
        Stream.of(FirmBillTypeEnum.values()).forEach(item->{
            //报关单录单
            doOutSql(readTdecCreateExcel(item.getExcelFile()) , StrUtil.concat(Boolean.TRUE ,BASE_EXCEL_PATH , item.getOutFile()), statisticsDate);
        });
    }

}
